package com.pro01.backend.mapper;

import com.pro01.backend.dto.MessageDTO;
import com.pro01.backend.pojo.Message;
import com.pro01.backend.pojo.MessageConversation;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface MessageMapper {

    // 发送私信
    @Insert("""
        INSERT INTO messages (sender_id, receiver_id, content, status, created_at, update_time)
        VALUES (#{senderId}, #{receiverId}, #{content}, #{status}, NOW(), NOW())
    """)
    int insertMessage(Message message);

    // 查询收件箱
    @Select("""
    SELECT m.*, u.username AS senderUsername
    FROM messages m
    LEFT JOIN users u ON m.sender_id = u.id
    WHERE m.receiver_id = #{receiverId}
      AND m.status != 'DELETED'
    ORDER BY m.created_at DESC
    LIMIT #{offset}, #{pageSize}
""")
    List<Message> selectReceivedMessages(@Param("receiverId") Long receiverId,
                                         @Param("offset") int offset,
                                         @Param("pageSize") int pageSize);

    @Select("""
    SELECT COUNT(*)
    FROM messages
    WHERE receiver_id = #{receiverId}
      AND status != 'DELETED'
""")
    int countReceivedMessages(@Param("receiverId") Long receiverId);

    // 查询发件箱
    @Select("""
    SELECT m.*, u.username AS receiverUsername
    FROM messages m
    LEFT JOIN users u ON m.receiver_id = u.id
    WHERE m.sender_id = #{senderId}
      AND m.status != 'DELETED'
    ORDER BY m.created_at DESC
    LIMIT #{offset}, #{pageSize}
""")
    List<Message> selectSentMessages(@Param("senderId") Long senderId,
                                     @Param("offset") int offset,
                                     @Param("pageSize") int pageSize);

    @Select("""
    SELECT COUNT(*)
    FROM messages
    WHERE sender_id = #{senderId}
      AND status != 'DELETED'
""")
    int countSentMessages(@Param("senderId") Long senderId);

    // 标记已读
    @Update("""
    UPDATE messages
    SET status = 'READ', update_time = NOW()
    WHERE id = #{messageId}
      AND receiver_id = #{receiverId}
      AND status = 'UNREAD'
""")
    int markMessageAsRead(@Param("messageId") Long messageId,
                          @Param("receiverId") Long receiverId);

    // 删除私信
    @Update("""
    UPDATE messages
    SET status = 'DELETED', update_time = NOW()
    WHERE id = #{messageId}
      AND (sender_id = #{userId} OR receiver_id = #{userId})
      AND status != 'DELETED'
""")
    int deleteMessage(@Param("messageId") Long messageId,
                      @Param("userId") Long userId);

    // 查询未读消息数量
    @Select("""
    SELECT COUNT(*)
    FROM messages
    WHERE receiver_id = #{receiverId}
      AND status = 'UNREAD'
""")
    int countUnreadMessages(@Param("receiverId") Long receiverId);

    // 会话列表
    @Select("""
    SELECT
      CASE
        WHEN m.sender_id = #{userId} THEN m.receiver_id
        ELSE m.sender_id
      END AS otherUserId,
      
      CASE
        WHEN m.sender_id = #{userId} THEN ru.username
        ELSE su.username
      END AS otherUsername,
      
      MAX(m.created_at) AS lastMessageTime,
      MAX(m.id) AS lastMessageId,  -- 拿到最新一条消息ID
      SUM(CASE WHEN m.receiver_id = #{userId} AND m.status = 'UNREAD' THEN 1 ELSE 0 END) AS unreadCount
      
    FROM messages m
    LEFT JOIN users su ON m.sender_id = su.id
    LEFT JOIN users ru ON m.receiver_id = ru.id
    WHERE m.sender_id = #{userId} OR m.receiver_id = #{userId}
    GROUP BY otherUserId, otherUsername
    ORDER BY lastMessageTime DESC
    LIMIT #{offset}, #{pageSize}
""")
    List<MessageConversation> selectConversations(@Param("userId") Long userId,
                                                  @Param("offset") int offset,
                                                  @Param("pageSize") int pageSize);

    @Select("""
    SELECT COUNT(DISTINCT CASE
        WHEN sender_id = #{userId} THEN receiver_id
        ELSE sender_id
    END)
    FROM messages
    WHERE sender_id = #{userId} OR receiver_id = #{userId}
""")
    int countConversations(@Param("userId") Long userId);

    @Select("""
    SELECT content
    FROM messages
    WHERE id = #{messageId}
""")
    String getContentByMessageId(@Param("messageId") Long messageId);

    // 拉取与对方的聊天记录
    @Select("""
    SELECT *
    FROM messages
    WHERE 
      ((sender_id = #{userId} AND receiver_id = #{otherUserId})
      OR (sender_id = #{otherUserId} AND receiver_id = #{userId}))
      AND status != 'DELETED'
    ORDER BY created_at ASC
    LIMIT #{offset}, #{pageSize}
""")
    List<Message> selectConversation(@Param("userId") Long userId,
                                     @Param("otherUserId") Long otherUserId,
                                     @Param("offset") int offset,
                                     @Param("pageSize") int pageSize);

    @Select("""
    SELECT COUNT(*)
    FROM messages
    WHERE 
      ((sender_id = #{userId} AND receiver_id = #{otherUserId})
      OR (sender_id = #{otherUserId} AND receiver_id = #{userId}))
      AND status != 'DELETED'
""")
    int countConversation(@Param("userId") Long userId,
                          @Param("otherUserId") Long otherUserId);


    @Select("""
    SELECT 
        m.*, 
        sender.username AS senderUsername, 
        receiver.username AS receiverUsername
    FROM messages m
    JOIN users sender ON m.sender_id = sender.id
    JOIN users receiver ON m.receiver_id = receiver.id
    WHERE 
      ((m.sender_id = #{userId} AND m.receiver_id = #{otherUserId})
      OR (m.sender_id = #{otherUserId} AND m.receiver_id = #{userId}))
      AND m.status != 'DELETED'
    ORDER BY m.created_at ASC
    LIMIT #{offset}, #{pageSize}
""")
    List<MessageDTO> selectConversationDTO(@Param("userId") Long userId,
                                           @Param("otherUserId") Long otherUserId,
                                           @Param("offset") int offset,
                                           @Param("pageSize") int pageSize);

    // 初始化聊天
    @Select("""
    SELECT COUNT(*) FROM messages
    WHERE (
      (sender_id = #{userId1} AND receiver_id = #{userId2})
      OR (sender_id = #{userId2} AND receiver_id = #{userId1})
    )
    AND status != 'DELETED'
""")
    int countConversationExists(@Param("userId1") Long userId1,
                                @Param("userId2") Long userId2);

    @Insert("""
    INSERT INTO messages (sender_id, receiver_id, content, status, created_at, update_time)
    VALUES (#{senderId}, #{receiverId}, #{content}, 'READ', NOW(), NOW())
""")
    int insertSystemMessage(@Param("senderId") Long senderId,
                            @Param("receiverId") Long receiverId,
                            @Param("content") String content);

}
